import pymysql
import re
from .date import getNow, getTimestamp
from collections import namedtuple

EnTest = namedtuple("EnTest", "id, user, passtime, passfile, starttime")

conn = pymysql.connect(
    host="localhost", user="root", password="root", database="learn"
)
# conn = pymysql.connect(host='192.168.0.106', user='root', password='123456', database='learn')


# 获取所有wtest的测试记录
def getAllTestData(user=None):
    cursor = conn.cursor()
    condition = "" if not user else r"where user='%s'" % user
    cursor.execute("select * from wtest %s" % condition)
    data = cursor.fetchall()

    cursor.close()

    return data


def writeTestData(user, starttime, passfile, type, book):
    cursor = conn.cursor()
    passtime = getNow()
    sql = (
        r"insert into wtest (user,  starttime, passtime, passfile, type, book) values ('%s', '%s', '%s', '%s', '%s', '%s')"
        % (user, starttime, passtime, passfile, type, book)
    )
    print("sql------------>", sql)
    count = None
    try:
        count = cursor.execute(sql)
        conn.commit()
    except Exception as e:
        print("write db fail::", e)
        conn.rollback()
    finally:
        cursor.close()

    success = count > 0
    if success:
        print("测试记录写入数据库成功!")
    else:
        print("测试记录写入数据库失败!")

    print("count-------->", count)
    return success


def getTestDataById(id=None):
    if not id:
        print("缺少必要参数id")
        return False

    sql = r"select * from wtest where id=%d" % id
    cursor = conn.cursor()
    cursor.execute(sql)
    row = cursor.fetchone()
    print(row, "<--------row")
    # 查询不到结果
    if not row:
        print("查询不到 id = %s 的数据" % id)
        return False
    etest = EnTest(*row)
    return etest


def delTestDataById(id=None):
    if not id:
        print("缺少必要参数id")
        return False

    sql = "delete from wtest where id=%d" % id
    cursor = conn.cursor()
    count = cursor.execute(sql)
    print("count--->", count)
    success = count > 0

    print("删除数据(id = %s)%s!" % (id, "成功" if success else "失败"))

    return success


def getLatestTest(user=None):
    testLs = getAllTestData(user)
    if len(testLs) == 0:
        print("没有任何测试记录...")
        return False

    # 获取最新的记录
    # 测试数据列表映射为时间戳列表
    timestampLs = [getTimestamp(atest[2]) for atest in testLs]
    maxTimestamp = max(timestampLs)
    maxIndex = timestampLs.index(maxTimestamp)
    latestIndex = maxIndex
    latestTest = testLs[latestIndex]
    latestTestObj = EnTest(*latestTest)
    # return latestTest
    return latestTestObj


def getAllUser():
    cursor = conn.cursor()
    sql = "select distinct user from wtest"
    cursor.execute(sql)
    data = cursor.fetchall()
    users = ["".join(userRow) for userRow in data]
    cursor.close()
    return users


def getDateVal(testObj):
    noNumberRe = re.compile(r"\D")
    fileName = testObj.passfile
    # dateStr = fileName.replace('words', '').replace('.txt', '')
    dateStr = noNumberRe.sub("", fileName)
    dateVal = int(dateStr)

    return dateVal


def getRank():
    users = getAllUser()
    userLatestTestLs = [getLatestTest(user) for user in users]
    userLatestTestLs.sort(key=getDateVal, reverse=True)
    return userLatestTestLs


def main():
    # writeTestData('nami', '2023-12-12 12:22:11', 'words0909.txt')
    # data = getAllTestData('lufy')

    # etest = getTestDataById(222)
    # print(etest)

    # delTestDataById(222)
    data = getAllTestData()
    print("========>data:", data)

    latestTest = getLatestTest("yang")
    print("latestTest-->", latestTest, latestTest.passfile)

    users = getAllUser()
    print("users--->", users)

    userLatestTestLs = getRank()
    print("userLatestTestLs----------->", userLatestTestLs)


if __name__ == "__main__":
    main()
